SQL Server Row Count for all Tables in a Database

By:   |   Updated: 2021-04-21   |   Comments (36)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Scripts


Problem

I am a database tester and one of my tasks involves getting row counts from all the tables in the source database and comparing it against the corresponding table row counts in the target database. How do I get the row counts from all the tables in a SQL Server Database? What are the different approaches to get this information?  Check out this tip to get these questions and more answered.

Solution

It is a common step in any ETL project to validate the row counts between source and target databases as part of the testing phase. Getting the row count from each table one by one and comparing and consolidating the results can be a tedious task. Hence any script/solution which can get the row count information from all the tables in a database can be really helpful and effective thereby considerably reducing the effort involved. In this tip we will see four different approaches to get the row counts from all the tables in a SQL Server database.

Let's take a look at each of the approaches:

  • sys.partitions Catalog View
  • sys.dm_db_partition_stats Dynamic Management View (DMV)
  • sp_MSforeachtable System Stored Procedure
  • COALESCE() Function

Approach 1: sys.partitions Catalog View

sys.partitions is an Object Catalog View and contains one row for each partition of each of the tables and most types of indexes (Except Fulltext, Spatial, and XML indexes). Every table in SQL Server contains at least one partition (default partition) even if the table is not explicitly partitioned.

The T-SQL query below uses the sys.partitions catalog view to capture the row counts for all tables in a database.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

Below are the highlights of this approach:

  1. Requires membership in the public role.
  2. Can be used even when working with source systems which offer limited privileges such as read-only.

Approach 2: sys.dm_db_partition_stats Dynamic Management View (DMV)

sys.dm_db_partition_stats is a Dynamic Management View (DMV) which contains one row per partition and displays the information about the space used to store and manage different data allocation unit types - IN_ROW_DATA, LOB_DATA and ROW_OVERFLOW_DATA.

The T-SQL query below uses the sys.dm_db_partition_stats DMV to capture the row counts for all tables in a database.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
            ON sOBJ.object_id = sdmvPTNS.object_id
WHERE 
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

Below are the highlights of this approach:

  1. VIEW DATABASE STATE permissions are required in the database.
  2. The values in the sys.dm_db_partition_stats DMV are reset on server restart or when an object/partition is dropped and recreated.

In general, querying the Dynamic Management Views (DMVs), requires VIEW SERVER STATE or VIEW DATABASE STATE permissions based on the Dynamic Management View/Function which is being queried.

Approach 3: sp_MSforeachtable System Stored Procedure

sp_MSforeachtable is an undocumented system stored procedure which can be used to iterate through each of the tables in a database. In this approach we will get the row counts from each of the tables in a given database in an iterative fashion and display the record counts for all the tables at once.

The T-SQL query below uses the sp_MSforeachtable system stored procedure to iterate through each of the tables to capture the row count for all the tables in a database.

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
GO

Below are the highlights of this approach:

  1. This is an iterative approach which captures the row count for each of the individual tables, puts them together and displays the results for all the tables.
  2. sp_MSforeachtable is an undocumented system stored procedure.
  3. This approach can be used for testing purposes but it is not recommended for use in any production code. sp_MSforeachtable is an undocumented system stored procedure and may change anytime without prior notification from Microsoft.

Approach 4: COALESCE() Function

The COALESCE() function is used to return the first non-NULL value/expression among its arguments. In this approach we will build a query to get the row count from each of the individual tables with UNION ALL to combine the results and run the entire query.

The T-SQL query below uses the COALESCE() function to iterate through each of the tables to dynamically build a query to capture the row count from each of the tables (individual COUNT queries combined using UNION ALL) and provides the row counts for all the tables in a database.

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

Below are the highlights of this approach:

  1. Can be used in cases where the number of tables is huge like say few hundred tables. This query can be modified to capture the row counts from a set of tables at one time instead of all the tables which might otherwise put a lot of load on the system.
  2. Can be used even when working with source systems which offer limited privileges such as read-only.

Sample Output

Below is sample output from AdventureWorksDW database. Note - the results in your AdventureWorksDW database might vary slightly from what is displayed below due to various reasons like the version of database being used, any changes/data manipulation done on the database for testing purposes, etc.

AdventureWorks Row Count
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dattatrey Sindol Dattatrey Sindol has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2021-04-21

Comments For This Article




Tuesday, March 22, 2022 - 4:36:52 AM - Barak810 Back To Top (89912)
We can ommit the SUM / grouping for indexes 0, 1.
A heap has no clustered index.

Why should we care?
We can show formatted text and sort by rows. Sorting by the formatted numbers has 9 before 27,000,000.


SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name) AS [TableName]
, format(p.Rows, 'N0') AS [RowCount]
FROM sys.objects o
INNER JOIN sys.partitions p ON o.object_id = p.object_id
WHERE o.type = 'U' AND o.is_ms_shipped = 0x0 AND index_id < 2 -- 0:Heap, 1:Clustered
ORDER BY p.rows desc

Thursday, August 12, 2021 - 1:30:10 PM - Ian Back To Top (89121)
Maybe just my data but your Approach 1 generates a multiple of the correct number of rows as I've got multiple partitions for the Object_id.

Got round that issue by adding in a Distinct, replacing SUM(sPTN.Rows) with sPTN.Rows) and removing the Group by

Saturday, July 17, 2021 - 7:35:55 AM - Dave Back To Top (89024)
How do I run these queries using open query?
Thanks

Wednesday, May 5, 2021 - 4:39:13 PM - John F Miner III Back To Top (88651)
Hi Datta,

I noticed that one of my favorite ways to get rows counts is not in your list.

I have to give credit when it is due to Thomas Grosher, fellow MVP for showing me this trick.

-- Use extended properties
select
s.name as the_schema_name,
t.name as the_table_name,
OBJECTPROPERTYEX(t.object_id, 'Cardinality') as the_row_count
from
sys.tables t
join
sys.schemas s
on
t.schema_id = s.schema_id
where
is_ms_shipped = 0;
go


I am curious about the execution times between all the above techniques in a POC environment that has large tables.

If you have time, that might be an interesting addition. Sample code to use before tests. Do not run in production.

-- Show time & i/o
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO

-- Remove clean buffers & clear plan cache
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

I think you might find this system function is extremely quick.

Sincerely

John Miner
The Crafty DBA

Wednesday, April 21, 2021 - 10:26:41 AM - Tina Somot Back To Top (88577)
I ran all 4 scripts on SQL server 2019 and they worked perfectly. I learned a great deal on the background of each script. Thank you!

Wednesday, November 11, 2020 - 6:46:20 PM - Gothan Geek Back To Top (87788)
Thank you for the technique using "COALESCE". It works flawlessly in my Python script that uses pyodbc module to query table record counts from SQL Server.

Tuesday, June 12, 2018 - 2:32:13 PM - Leif Peterson Back To Top (76200)

*FIXED*, Row count was wrong, now I pull the value the primary key index only

EXEC sp_MSforeachdb '

USE ?

--ALL FK columns with INDEX indication

select DB_NAME(),s.name schema_name,o.name object_name,c.name column_name, fk.name FK_name, i.name index_name, p.rows

from sys.schemas s

INNER JOIN sys.objects o ON s.[schema_id] = o.[schema_id]

INNER JOIN sys.partitions p ON p.object_id = o.object_id

INNER JOIN sys.indexes ip ON ip.object_id=o.object_id and p.index_id = ip.index_id and ip.is_primary_key=1

INNER JOIN sys.columns c on c.object_id = o.object_id

INNER JOIN sys.foreign_key_columns fkc on fkc.parent_object_id = o.object_id and fkc.parent_column_id = c.column_id

INNER JOIN sys.foreign_keys fk on fk.object_id = fkc.constraint_object_id

LEFT JOIN sys.index_columns ic on o.object_id = ic.object_id and c.column_id = ic.column_id

LEFT JOIN sys.indexes i on i.index_id = ic.Index_id and i.object_id = ic.object_id

WHERE i.name IS NULL

AND p.rows>5000

order by s.name,o.name,c.column_id,fk.name'

Tuesday, June 12, 2018 - 1:46:43 PM - Leif Peterson Back To Top (76199)

Thanks for the help.  I found that this and other examples returned records where the column was in a compound index so I wrote this to only show FK columns that were not part of any index.  Also wrapped in foreachDB.  Comment the WHERE clause and HAVING to see all records.

EXEC sp_MSforeachdb '

USE ?

--ALL FK columns with INDEX indication

select s.schema_id,s.name schema_name,o.object_id,o.name object_name,c.column_id,c.name column_name, fk.name FK_name, i.name index_name, SUM(p.rows) row_count

from sys.schemas s

INNER JOIN sys.objects o ON s.[schema_id] = o.[schema_id]

INNER JOIN sys.partitions p ON p.object_id = o.object_id

INNER JOIN sys.columns c on c.object_id = o.object_id

INNER JOIN sys.foreign_key_columns fkc on fkc.parent_object_id = o.object_id and fkc.parent_column_id = c.column_id

INNER JOIN sys.foreign_keys fk on fk.object_id = fkc.constraint_object_id

LEFT JOIN sys.index_columns ic on o.object_id = ic.object_id and c.column_id = ic.column_id

LEFT JOIN sys.indexes i on i.index_id = ic.Index_id and i.object_id = ic.object_id

WHERE i.name IS NULL

GROUP BY s.schema_id,s.name ,o.object_id,o.name ,c.column_id,c.name , fk.name , i.name

HAVING SUM(p.rows)>5000

order by s.name,o.name,c.column_id,fk.name'

 

I also wrote this...

--ALL INDEXES with PK / FK indication

select s.schema_id,s.name schema_name,o.object_id,o.name object_name,c.column_id,c.name column_name,i.object_id,i.index_id,i.name index_name, ic.index_column_id, fk.name FK_name

from sys.schemas s

INNER JOIN sys.objects o ON s.[schema_id] = o.[schema_id]

INNER JOIN sys.columns c on c.object_id = o.object_id

INNER JOIN sys.indexes i on i.object_id = o.object_id

INNER JOIN sys.index_columns ic on o.object_id = ic.object_id and i.index_id = ic.Index_id and c.column_id = ic.column_id

LEFT JOIN sys.foreign_key_columns fkc on fkc.parent_object_id = o.object_id and fkc.parent_column_id = c.column_id

LEFT JOIN sys.foreign_keys fk on fk.object_id = fkc.constraint_object_id

LEFT JOIN sys.key_constraints kc on kc.parent_object_id = o.object_id and i.index_id = kc.unique_index_id

where o.is_ms_shipped=0

order by s.name,o.name,c.column_id,i.name

 

Sunday, February 18, 2018 - 1:40:57 AM - ismail Back To Top (75231)

 Can i select all the record inserted/updated by last hour from all table from particular database.

 

Thursday, January 25, 2018 - 5:44:35 AM - tushank dhall Back To Top (75035)

 Hi Team,

 

   Any way to fetch all table name and table count in sql server 6.5 ??

 

Regards,

Tushank

Friday, June 30, 2017 - 10:18:20 AM - Brian Back To Top (58609)

 Your piece, "SQL Server Row Count for all Tables in a Database" is unhelpful for me, because I don't ess how to use any of the techniques. (I tried them all)

Do I need to have a specific node selected in the Object Explorer? SInce my Object Explorer has many databases, how do i use any of these techniques in a SQL Managment Stidio query?

 

Not every one who reads this article are going to have the epertise that you are assuming.

 

 

Friday, June 23, 2017 - 12:56:46 PM - Himanshu Back To Top (58035)

 how about this ?

 

declare @string varchar(max)=''
select @string+= 'select ''' + name + '''  as Name,count(*) as count  from  '+ name + '  union all ' from sys.tables
select @string=Substring(@string,1,len(@string)-9)
execute( @string)

Thursday, June 22, 2017 - 8:39:34 AM - Dave Potts Back To Top (57922)

It looks like you are looking for a SQL solution but if you want to automate things it may be worth looking at using Powershell in Windows. 

To display the information: 

'Get-ChildItem -Path "SQLSERVER:\SQL\\DEFAULT\Databases\\Tables\" | Select-Object -Property Schema, Name, RowCount | Format-Table -AutoSize' 

This can be easily adapted to store the output as values and compare them:

$a = Get-ChildItem -Path "SQLSERVER:\SQL\\DEFAULT\Databases\\Tables\" | Select-Object -Property Schema, Name, RowCount 

 

$a = Get-ChildItem -Path "SQLSERVER:\SQL\\DEFAULT\Databases\\Tables\" | Select-Object -Property Schema, Name, RowCount 

Compare-Object $a $b

(Note : This uses the SQLPS PowerShell provider. Try 'Import-Module SQLPS' if you get "A drive with the name 'SQLSERVER' does not exist" )

 

Thursday, June 22, 2017 - 7:28:12 AM - Jeff Moden Back To Top (57919)

@Vinod,

ALL tables have at least 1 partition.

Thursday, June 22, 2017 - 4:48:12 AM - wilfred van Dijk Back To Top (57906)

Additional approach: use column rowcnt in sysindexes:

select sum(rowcnt) from sysindexes where indid < 2

if you want to eliminate system tables, use this query:

select sum(a.rowcnt) from sysindexes a join sys.tables b on a.id = b.object_id where a.indid < 2 and b.is_ms_shipped = 0

 

 

 

 

Sunday, February 26, 2017 - 12:51:38 AM - jayaram Back To Top (46691)

 

 Awesome,

 

This query helped me a lot.

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

I also applied like condition by specifiying if the given table name has qos string then only display the count.

Thank you so much for such a beautiful query,

 

Wednesday, May 18, 2016 - 11:30:10 AM - Vinod Back To Top (41510)

The above options might all works well in case of tables partitioned / columns indexed. But what about tables where its not partitioned ? The below query simply fetches the record count per table irrespective of volume.

-- query

SELECT  SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2

Friday, August 16, 2013 - 8:19:22 AM - SteveC Back To Top (26347)

Very useful article

Thanks for publishing it 

Cheers,

SteveC.

Wednesday, March 6, 2013 - 12:09:56 PM - Jeff Back To Top (22599) 
You note that you sould not use depricated features, neither should you use undocumented features.
 
 
DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
GO

Friday, February 1, 2013 - 11:41:17 AM - komala Back To Top (21864)

How to take backup database from sql server

Sunday, January 27, 2013 - 6:38:12 AM - Jimmi Beloitt Back To Top (21733)

http://www.bigator.com/2013/01/23/mssql-find-total-records-of-each-table-in-given-database/

Friday, June 22, 2012 - 1:24:02 AM - Dattatrey Sindol (Datta) Back To Top (18168)

Hi Murthy,

Your query works as well. Infact the 2nd approach presented above is on these lines with a couple additional filters.

Best Regards,

Dattatrey Sindol (Datta)

http://dattatreysindol.com/

Thursday, June 21, 2012 - 6:51:13 PM - murthy Back To Top (18163)

I think we can also using this query :

SELECT DISTINCT s.name as SchemaName,OBJECT_NAME(o.OBJECT_ID) AS TableName,p.row_count
FROM
SYS.objects o JOIN SYS.schemas s
   ON o.schema_id=s.schema_id
        JOIN sys.dm_db_partition_stats p
   ON o.object_id=p.object_id
WHERE o.type LIKE 'U'
AND s.name LIKE 'schema_name'
ORDER BY TableName

Friday, June 8, 2012 - 3:17:33 AM - Prashant Back To Top (17876)

@Reckon, your query will not work for table which will not have any PK/index.

Thursday, March 22, 2012 - 1:56:34 PM - Jason Back To Top (16582)

I modified sp_spaceused to return correct schema name. In the parsename function, you can recode calling parsename. Imagine you have two objects of the same name in two different schema. That provides row count, space used including index etc.

Regards,

Jason

http://dbace.us

 

Thursday, March 22, 2012 - 1:17:01 AM - Dattatrey Sindol Back To Top (16575)

Hi Vipul,

When it comes to views, it gets a little tricky depending on the type of view(s) you are dealing. Based on your preferences mentioned above, I would suggest you use Approach# 4 mentioned above (ofcourse you need to change type = 'U' to type = 'V') with the following considerations in mind:

  • Identify the complexity of the views based on number of tables involved, JOIN conditions, etc. Basically evaluate the complexity based on the T-SQL present inside your view. Group the views into Simple, Medium, & Complex.
  • You could group few views and fire the query in Approach# 4.
  • As the view complexity increases then the number of veiws grouped in each query should be reduced accordingly.
  • In some scenarios based on my experience, source systems expose the data to downstream applications in the form of views which map 1:1 to the underlying tables without any filters/transformations. In this type of scenario, I don't see any much performance impact and Approach# 4 can be used here as well.

Hope that helps.

Monday, March 19, 2012 - 4:00:16 PM - vipul Back To Top (16515)

what if I want to capture row counts off of SQL Views?  Don't want to use any deprecated or unsupported functionality.  No MSForeach, or sysindexes.  suggestions?

Monday, November 14, 2011 - 8:56:46 AM - Prabhakar Back To Top (15102)

Hello Datta,

Thanks for the info.

Happy coding,

Prabhakar J

Saturday, November 12, 2011 - 8:34:21 AM - Jeremy Kadlec Back To Top (15088)

Everyone,

Thank you for all of the comments on this tip.  It is good to see how other SQL Server Professionals in the community address this need.

Thank you,
Jeremy Kadlec

Saturday, November 12, 2011 - 1:18:19 AM - Dattatrey Sindol Back To Top (15086)

Hello Adam,

Your approach works well too! The differences I see are storing the results in Temp Table instead of Table Variable, and use of sp_spaceused system stored procedure. However, with the use of sp_spaceused, it does not provide the schema related information.

Thanks for sharing.

 

Best Regards,

Datta

Saturday, November 12, 2011 - 1:08:24 AM - Dattatrey Sindol Back To Top (15085)

Hello Prabhakar,

Please note that "sys.sysindexes" is a compatibility view provided for backward compatibility. We should avoid using this as this feature will be removed in future versions of SQL Server.

You can refer to the following URLs for more details:

Best Regards,

Datta

Friday, November 11, 2011 - 5:43:34 PM - Adam M Back To Top (15083)

Hello,

I use foreachtable but do it a bit differently. I found count(*) could really take a long time so I used:

 

IF OBJECT_ID('tempdb..#sizeo') IS NOT NULL 

DROP TABLE #sizeo

 

CREATE TABLE #sizeo (

NAME VARCHAR(100),

ROWS INT,

reserved VARCHAR(50),

DATA VARCHAR(50),

index_size VARCHAR(50), 

unused VARCHAR(50)

)

 

EXEC sp_msforeachtable '

insert into #sizeo

EXEC sp_spaceused ''?''

'

SELECT name, ROWS FROM #sizeo

 

I enjoyed the other ways to find it though. Thanks!

Friday, November 11, 2011 - 5:09:28 PM - Prabhakar Back To Top (15082)

Hello Friends,

I Reckon, the below query would do the trick.

SELECT DISTINCT OBJECT_NAME(ID),ID,ROWCNT FROM SYSINDEXES WHERE INDID < 2

Regards,

Prabhakar J

Friday, November 11, 2011 - 1:27:30 PM - Dattatrey Sindol Back To Top (15080)

Thanks for the feedback GP and posting one more way to get the row count (manual approach).

However, as Jason called out, quite often, we need automatic ways to get row counts for for performing tasks like Auditing, Row Count Validation, etc.

Thank you both for your feedback and additional information.

Friday, November 11, 2011 - 11:58:39 AM - Jason Back To Top (15079)

GP, that is manually. We often need this automatic way (insert into a data history, for example). DBA has to do work most efficient way.

Jason

http://usa.redirectme.net

Friday, November 11, 2011 - 7:42:53 AM - GAJANAN Back To Top (15077)

very informative

and you would find 5th option pretty simple

- View Object Explorer details
- Click Tables
- and RMC on Headers of the table and Click ROw Count it will give you row count for all the tbale objects.

Thanks
GP














get free sql tips
agree to terms